
USE QuanLyKho
GO
CREATE TRIGGER afterInsertNhapKhoDetail
ON nhapKhoDetail
AFTER  INSERT 
AS
BEGIN
	DECLARE @maHang varchar(50);
	DECLARE @soLuong int;
	DECLARE @donGia float;
	select @maHang=maHang,@soLuong=soLuong ,@donGia=donGia from INSERTED;
	Update hanghoa set soLuong=soLuong+@soLuong,
		donGia= ROUND((donGia*soLuong+ @donGia*@soLuong)/(soLuong+@soLuong),2)
	where maHang=@maHang
END

insertNhapKhoDetail 'hn001',12,10,30 
GO
insertNhapKhoDetail 'hn001',12,2,4 
GO
insertNhapKhoDetail 'hn001',12,2,4 

select * from hangHoa

select ROUND(12.55644,2.2)

--------------------------------------------------------
CREATE TRIGGER afterInsertXuatKhoDetail
ON xuatKhoDetail
AFTER  INSERT 
AS
BEGIN
	DECLARE @maHang varchar(50);
	DECLARE @soLuong int;
	select @maHang=maHang,@soLuong=soLuong  from INSERTED;
	Update hanghoa set soLuong=soLuong-@soLuong	where maHang=@maHang
END


ALTER PROC InsertNhapXuatDetail
 @maHang varchar(50),
 @soLuongNhap int,
 @SoLuongXuat int,
 @tongTienNhap float,
 @TongTienXuat float
AS
BEGIN
	DECLARE @c int;
	SET @c= (select count(maHang) from NhapXuatDetail where maHang=@maHang and DATEPART(yyyy,Ngay)=DATEPART (yyyy,GETDATE()) and DATEPART(dy,Ngay)=DATEPART(dy,GETDATE() ) )
	if (@c=0)
		INSERT INTO NhapXuatDetail values (@maHang,GETDATE(),@soLuongNhap,@SoLuongXuat,@tongTienNhap, @TongTienXuat);
	else
		BEGIN
			update NhapXuatDetail SET
			soLuongNhap=soLuongNhap+@soLuongNhap,
			SoLuongXuat=SoLuongXuat+SoLuongXuat,
			tongTienNhap=tongTienNhap+@tongTienNhap,
			TongTienXuat=TongTienXuat+@TongTienXuat
			where maHang=@maHang and DATEPART(yyyy,Ngay)=DATEPART (yyyy,GETDATE()) and DATEPART(dy,Ngay)=DATEPART(dy,GETDATE() )
		END

END

InsertNhapXuatDetail 'hn001',100,56,5000,56

select DATEPART(dy,GETDATE() )